﻿using System;
using System.Collections.Generic;
using System.Text;

namespace Common.IO
{
    /// <summary>
    /// EXCEL文件操作类
    /// </summary>
    public class ExcelOperator
    {
        /// <summary>
        /// 为文本设置样式
        /// </summary>
        /// <param name="ws"></param>
        public void SetCellProperty(Microsoft.Office.Interop.Excel.Worksheet ws)
        {

            string name = "宋体";

            int size = 10;

            ws.Cells.Font.Name = name;

            ws.Cells.Font.Size = size;
            ws.Columns.ColumnWidth = 10;
            //  ws.Cells.

        }
        /// <summary>
        /// 为部分文本设置居中对齐方式
        /// </summary>
        /// <param name="ws"></param>
        /// <param name="x1"></param>
        /// <param name="y1"></param>
        /// <param name="x2"></param>
        /// <param name="y2"></param>
        public void SetRangeLayOut(Microsoft.Office.Interop.Excel.Worksheet ws, int x1, int y1, int x2, int y2)
        {
            ws.get_Range(ws.Cells[y1, x1], ws.Cells[y2, x2]).HorizontalAlignment = Microsoft.Office.Interop.Excel.Constants.xlCenter;
        }

        /// <summary>
        /// 为部分文本添加表格样式
        /// </summary>
        /// <param name="borders"></param>
        public void SetBorder(Microsoft.Office.Interop.Excel.Range excelRange)
        {
            //加入表格边框
            Microsoft.Office.Interop.Excel.Borders pborders = excelRange.Borders;

            //设置左边框
            pborders.get_Item(Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeLeft);
            pborders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
            pborders.Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin;
            pborders.ColorIndex = Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic;
            object pObject = excelRange.BorderAround(Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous, Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin, Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic, null);

            ////设置上边框线
            pborders.get_Item(Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeTop);
            pborders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
            pborders.Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin;
            pborders.ColorIndex = Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic;
            pObject = excelRange.BorderAround(Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous, Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin, Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic, null);

            ////设置下边框线
            pborders.get_Item(Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeBottom);
            pborders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
            pborders.Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin;
            pborders.ColorIndex = Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic;
            pObject = excelRange.BorderAround(Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous, Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin, Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic, null);

            ////设置右边框线
            pborders.get_Item(Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeRight);
            pborders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
            pborders.Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin;
            pborders.ColorIndex = Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic;
            pObject = excelRange.BorderAround(Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous, Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin, Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic, null);

            ////设置内格竖线
            pborders.get_Item(Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideVertical);
            pborders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
            pborders.Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin;
            pborders.ColorIndex = Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic;
            pObject = excelRange.BorderAround(Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous, Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin, Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic, null);

            ////设置内格横边框线
            pborders.get_Item(Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideHorizontal);
            pborders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
            pborders.Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin;
            pborders.ColorIndex = Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic;
            pObject = excelRange.BorderAround(Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous, Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin, Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic, null);
        }
        /// <summary>
        /// 合并单元格  
        /// </summary>
        /// <param name="ws"></param>
        /// <param name="x1"></param>
        /// <param name="y1"></param>
        /// <param name="x2"></param>
        /// <param name="y2"></param>
        public void UniteCells(Microsoft.Office.Interop.Excel.Worksheet ws, int x1, int y1, int x2, int y2)
        {
            ws.get_Range(ws.Cells[y1, x1], ws.Cells[y2, x2]).Merge(Type.Missing);
        }





        /*

       //下面是一个调用的列子，当时是在外面的dll中使用的方法

using System;
using System.Collections.Generic;

namespace ServerHelper.DAL
{
    /// <summary>
    /// 逐日盯市月报
    /// </summary>
    internal class ZhuRiDingShiMonth
    {
        Microsoft.Office.Interop.Excel.Range excelRange = null;
        public void Write(int accountid, int month, int year, string path)
        {
            Microsoft.Office.Interop.Excel.ApplicationClass xlApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
            xlApp.Visible = false;
            object objMissing = System.Reflection.Missing.Value;
            Microsoft.Office.Interop.Excel.Workbook mybook = (Microsoft.Office.Interop.Excel.Workbook)xlApp.Workbooks.Add(5);   //   1表示只建一个表

            mybook.Worksheets.Add(objMissing, objMissing, 1, objMissing);//添加sheet
            Microsoft.Office.Interop.Excel.Worksheet XHJYJSRB = (Microsoft.Office.Interop.Excel.Worksheet)mybook.Worksheets[1];
            Microsoft.Office.Interop.Excel.Worksheet PZHZ = (Microsoft.Office.Interop.Excel.Worksheet)mybook.Worksheets[2];
            Microsoft.Office.Interop.Excel.Worksheet CJMX = (Microsoft.Office.Interop.Excel.Worksheet)mybook.Worksheets[3];
            Microsoft.Office.Interop.Excel.Worksheet CCMX = (Microsoft.Office.Interop.Excel.Worksheet)mybook.Worksheets[4];
            Common.IO.ExcelOperator oper = new Common.IO.ExcelOperator();
            oper.SetCellProperty(XHJYJSRB);
            oper.SetCellProperty(PZHZ);
            oper.SetCellProperty(CJMX);
            oper.SetCellProperty(CCMX);
            XHJYJSRB.Name = "客户交易结算月报";
            PZHZ.Name = "品种汇总";
            CJMX.Name = "交易明细";
            CCMX.Name = "持仓明细";

            setZhuRiDingShiText(accountid, month, year, XHJYJSRB, oper);//ok
            setZhuRiDingShiCCMX(accountid, month, year, CCMX, oper);//ok
            setZhuRiDingShiCJMX(accountid, month, year, CJMX, oper);//ok
            setZhuRiDingShiPZHZ(accountid, month, year, PZHZ, oper);

            mybook.SaveAs(path, objMissing, objMissing, objMissing,   //Excel.XlSaveAsAccessMode.xlShared
            objMissing, objMissing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange,
            objMissing, objMissing, objMissing, objMissing,
            objMissing);
            mybook.Close(false, objMissing, objMissing);

            mybook = null;
            xlApp.Quit();
            xlApp = null;
        }


        /// <summary>
        /// 逐日盯市品种汇总
        /// </summary>
        private void setZhuRiDingShiPZHZ(int accountid, int month, int year, Microsoft.Office.Interop.Excel.Worksheet sheet, Common.IO.ExcelOperator oper)
        {
            int Row = 2;
            Row = this.WriteHead(accountid, month, year, sheet, oper, Row);
            Row = this.DoPZHZ(accountid, month, year, sheet, oper, Row);
            sheet.get_Range(sheet.Cells[1, 1], sheet.Cells[Row, 10]).RowHeight = 14;
        }
        /// <summary>
        /// 写入品种汇总
        /// </summary>
        /// <param name="accountid"></param>
        /// <param name="pdate"></param>
        /// <param name="sheet"></param>
        /// <param name="oper"></param>
        /// <param name="Row"></param>
        /// <returns></returns>
        private int DoPZHZ(int accountid, int month, int year, Microsoft.Office.Interop.Excel.Worksheet sheet, Common.IO.ExcelOperator oper, int Row)
        {
            List<ServerHelper.Model.T_Product_Summary_Month> details = new ServerHelper.Model.T_Product_Summary_Month().GetInfo(accountid, year, month);
            sheet.Cells[Row, 1] = "品种汇总";
            oper.UniteCells(sheet, 1, Row, 6, Row);
            oper.SetRangeLayOut(sheet, 1, Row, 6, Row);
            int _row = Row;
            Row = Row + 1;
            sheet.Cells[Row, 1] = "交易日期";
            oper.SetRangeLayOut(sheet, 1, Row, 1, Row);
            sheet.Cells[Row, 2] = "品种";
            oper.SetRangeLayOut(sheet, 2, Row, 2, Row);
            sheet.Cells[Row, 3] = "手数";
            oper.SetRangeLayOut(sheet, 3, Row, 3, Row);
            sheet.Cells[Row, 4] = "成交额";
            oper.SetRangeLayOut(sheet, 4, Row, 4, Row);
            sheet.Cells[Row, 5] = "手续费";
            oper.SetRangeLayOut(sheet, 5, Row, 5, Row);
            sheet.Cells[Row, 6] = "平仓盈亏";
            oper.SetRangeLayOut(sheet, 6, Row, 6, Row);

            Row = Row + 1;

            int? _volume = 0;
            decimal? fee = 0;
            decimal? benefit = 0;
            decimal? cje = 0;
            for (int i = 0; i < details.Count; i++)
            {
                if (details[i].Volume > 0)
                {
                    sheet.Cells[Row, 1] = details[i].TradingDay;
                    oper.SetRangeLayOut(sheet, 1, Row, 1, Row);

                    sheet.Cells[Row, 2] = details[i].ProductId;
                    oper.SetRangeLayOut(sheet, 2, Row, 2, Row);

                    sheet.Cells[Row, 3] = details[i].Volume;
                    oper.SetRangeLayOut(sheet, 3, Row, 3, Row);
                    _volume += details[i].Volume;
                    sheet.Cells[Row, 4] = details[i].TotalCost;
                    cje += details[i].TotalCost;
                    oper.SetRangeLayOut(sheet, 4, Row, 4, Row);
                    //   maichic += details[i].CloseAmount;
                    sheet.Cells[Row, 5] = details[i].Commission;
                    fee += details[i].Commission;
                    oper.SetRangeLayOut(sheet, 5, Row, 5, Row);

                    sheet.Cells[Row, 6] = details[i].CloseProfit;
                    benefit += details[i].CloseProfit;
                    oper.SetRangeLayOut(sheet, 6, Row, 6, Row);

                    Row++;
                }
            }
            int cashiocount = details.Count + 2;
            excelRange = sheet.get_Range(sheet.Cells[_row, 1], sheet.Cells[_row + cashiocount, 6]);
            oper.SetBorder(excelRange);

            sheet.Cells[_row + cashiocount, 1] = "合计";
            oper.SetRangeLayOut(sheet, 1, _row + cashiocount, 1, _row + cashiocount);
            sheet.Cells[Row, 2] = "";
            oper.SetRangeLayOut(sheet, 2, Row, 2, Row);
            sheet.Cells[Row, 3] = _volume;
            oper.SetRangeLayOut(sheet, 3, Row, 3, Row);
            sheet.Cells[Row, 4] = cje;
            oper.SetRangeLayOut(sheet, 4, Row, 4, Row);
            sheet.Cells[Row, 5] = fee;
            oper.SetRangeLayOut(sheet, 5, Row, 5, Row);
            sheet.Cells[Row, 6] = benefit;
            oper.SetRangeLayOut(sheet, 6, Row, 6, Row);
            Row = _row + cashiocount + 1;
            return Row;
        }



        /// <summary>
        /// 逐日盯市交易明细
        /// </summary>
        private void setZhuRiDingShiCJMX(int accountid, int month, int year, Microsoft.Office.Interop.Excel.Worksheet sheet, Common.IO.ExcelOperator oper)
        {
            int Row = 2;
            Row = this.WriteHead(accountid, month, year, sheet, oper, Row);
            Row = this.DoCJMX(accountid, month, year, sheet, oper, Row);
            sheet.get_Range(sheet.Cells[1, 1], sheet.Cells[Row, 12]).RowHeight = 14;
        }

        /// <summary>
        /// 写入交易明细
        /// </summary>
        /// <param name="accountid"></param>
        /// <param name="pdate"></param>
        /// <param name="sheet"></param>
        /// <param name="oper"></param>
        /// <param name="Row"></param>
        /// <returns></returns>
        private int DoCJMX(int accountid, int month, int year, Microsoft.Office.Interop.Excel.Worksheet sheet, Common.IO.ExcelOperator oper, int Row)
        {
            List<ServerHelper.Model.T_TradeDetail_Month> details = new ServerHelper.Model.T_TradeDetail_Month().GetInfo(accountid, year, month);
            sheet.Cells[Row, 1] = "交易明细";
            oper.UniteCells(sheet, 1, Row, 12, Row);
            oper.SetRangeLayOut(sheet, 1, Row, 12, Row);
            int _row = Row;
            Row = Row + 1;
            sheet.Cells[Row, 1] = "交易日期";
            oper.SetRangeLayOut(sheet, 1, Row, 1, Row);
            sheet.Cells[Row, 2] = "合约";
            oper.SetRangeLayOut(sheet, 2, Row, 2, Row);
            sheet.Cells[Row, 3] = "成交序号";
            oper.SetRangeLayOut(sheet, 3, Row, 3, Row);
            sheet.Cells[Row, 4] = "成交时间";
            oper.SetRangeLayOut(sheet, 4, Row, 4, Row);
            sheet.Cells[Row, 5] = "买/卖";
            oper.SetRangeLayOut(sheet, 5, Row, 5, Row);
            sheet.Cells[Row, 6] = "投机/套保";
            oper.SetRangeLayOut(sheet, 6, Row, 6, Row);
            sheet.Cells[Row, 7] = "成交价";
            oper.UniteCells(sheet, 7, Row, 7, Row);
            sheet.Cells[Row, 8] = "手数";
            oper.UniteCells(sheet, 8, Row, 8, Row);
            sheet.Cells[Row, 9] = "成交额";
            oper.SetRangeLayOut(sheet, 9, Row, 9, Row);
            sheet.Cells[Row, 10] = "开/平";
            oper.SetRangeLayOut(sheet, 10, Row, 10, Row);
            sheet.Cells[Row, 11] = "手续费";
            oper.SetRangeLayOut(sheet, 11, Row, 11, Row);
            sheet.Cells[Row, 12] = "平仓盈亏";
            oper.SetRangeLayOut(sheet, 12, Row, 12, Row);

            Row = Row + 1;

            int? _volume = 0;
            decimal? fee = 0;
            decimal? benefit = 0;
            decimal? cje = 0;
            for (int i = 0; i < details.Count; i++)
            {
                if (details[i].Volume > 0)
                {
                    sheet.Cells[Row, 1] = details[i].TradingDay;
                    oper.SetRangeLayOut(sheet, 1, Row, 1, Row);

                    sheet.Cells[Row, 2] = details[i].InstrumentId;
                    oper.SetRangeLayOut(sheet, 2, Row, 2, Row);

                    sheet.Cells[Row, 3] = details[i].TradeId.ToString();
                    oper.SetRangeLayOut(sheet, 3, Row, 3, Row);

                    sheet.Cells[Row, 4] = details[i].TradeTime;
                    oper.SetRangeLayOut(sheet, 4, Row, 4, Row);
                    //   maichic += details[i].CloseAmount;
                    sheet.Cells[Row, 5] = details[i].Direction;
                    oper.SetRangeLayOut(sheet, 5, Row, 5, Row);

                    sheet.Cells[Row, 6] = details[i].HedgeFlag;
                    oper.SetRangeLayOut(sheet, 6, Row, 6, Row);
                    //    maichic2 += details[i].CloseAmount;
                    sheet.Cells[Row, 7] = details[i].TradePrice;
                    oper.SetRangeLayOut(sheet, 7, Row, 7, Row);


                    sheet.Cells[Row, 8] = details[i].Volume;
                    oper.SetRangeLayOut(sheet, 8, Row, 8, Row);
                    _volume += details[i].Volume;

                    sheet.Cells[Row, 9] = details[i].TotalCost;
                    oper.SetRangeLayOut(sheet, 9, Row, 9, Row);
                    cje += details[i].TotalCost;

                    sheet.Cells[Row, 10] = details[i].OffsetFlag;
                    oper.SetRangeLayOut(sheet, 10, Row, 10, Row);

                    sheet.Cells[Row, 11] = details[i].Commission;
                    oper.SetRangeLayOut(sheet, 11, Row, 11, Row);
                    fee += details[i].Commission;

                    sheet.Cells[Row, 12] = details[i].CloseProfit;
                    oper.SetRangeLayOut(sheet, 12, Row, 12, Row);
                    benefit += details[i].CloseProfit;
                    Row++;
                }
            }
            int cashiocount = details.Count + 2;
            excelRange = sheet.get_Range(sheet.Cells[_row, 1], sheet.Cells[_row + cashiocount, 12]);
            oper.SetBorder(excelRange);

            sheet.Cells[_row + cashiocount, 1] = "合计";
            oper.SetRangeLayOut(sheet, 1, _row + cashiocount, 1, _row + cashiocount);
            sheet.Cells[Row, 2] = "";
            oper.SetRangeLayOut(sheet, 2, Row, 2, Row);
            sheet.Cells[Row, 3] = "";
            oper.SetRangeLayOut(sheet, 3, Row, 3, Row);
            sheet.Cells[Row, 4] = "";
            oper.SetRangeLayOut(sheet, 4, Row, 4, Row);
            sheet.Cells[Row, 5] = "";
            oper.SetRangeLayOut(sheet, 5, Row, 5, Row);
            sheet.Cells[Row, 6] = "";
            oper.SetRangeLayOut(sheet, 6, Row, 6, Row);
            sheet.Cells[Row, 7] = cje;
            oper.UniteCells(sheet, 7, Row, 7, Row);
            sheet.Cells[Row, 8] = _volume;
            oper.SetRangeLayOut(sheet, 8, Row, 8, Row);
            sheet.Cells[Row, 9] = cje;
            oper.SetRangeLayOut(sheet, 9, Row, 9, Row);

            sheet.Cells[Row, 10] = "";
            oper.SetRangeLayOut(sheet, 10, Row, 10, Row);
            sheet.Cells[Row, 11] = fee.ToString();
            oper.SetRangeLayOut(sheet, 11, Row, 11, Row);
            sheet.Cells[Row, 12] = benefit;
            oper.SetRangeLayOut(sheet, 12, Row, 12, Row);
            Row = _row + cashiocount + 1;
            return Row;
        }


        /// <summary>
        /// 逐日盯市持仓明细
        /// </summary>
        private void setZhuRiDingShiCCMX(int accountid, int month, int year, Microsoft.Office.Interop.Excel.Worksheet sheet, Common.IO.ExcelOperator oper)
        {
            int Row = 2;
            Row = this.WriteHead(accountid, month, year, sheet, oper, Row);
            Row = this.DoCCMX(accountid, month, year, sheet, oper, Row);
            sheet.get_Range(sheet.Cells[1, 1], sheet.Cells[Row, 10]).RowHeight = 14;
        }

        ///// <summary>
        ///// 逐日盯市平仓明细
        ///// </summary>
        //private void setZhuRiDingShiPCMX(int accountid, int month, int year, Microsoft.Office.Interop.Excel.Worksheet sheet, Common.IO.ExcelOperator oper)
        //{
        //    int Row = 2;
        //    string date = pdate.Replace("-", "");
        //    Row = this.WriteHead(accountid, month, year, sheet, oper, Row);
        //    Row = this.DoPCMX(accountid, date, sheet, oper, Row);
        //    sheet.get_Range(sheet.Cells[1, 1], sheet.Cells[Row, 7]).RowHeight = 14;
        //}

        ///// <summary>
        ///// 写入平仓明细
        ///// </summary>
        ///// <param name="accountid"></param>
        ///// <param name="pdate"></param>
        ///// <param name="sheet"></param>
        ///// <param name="oper"></param>
        ///// <param name="Row"></param>
        ///// <returns></returns>
        //private int DoPCMX(int accountid, string pdate, Microsoft.Office.Interop.Excel.Worksheet sheet, Common.IO.ExcelOperator oper, int Row)
        //{
        //    List<ServerHelper.Model.T_ClosePositionDetail_History> details = new ServerHelper.Model.T_ClosePositionDetail_History().GetInfo(accountid, pdate);
        //    sheet.Cells[Row, 1] = "平仓明细";
        //    oper.UniteCells(sheet, 1, Row, 7, Row);
        //    oper.SetRangeLayOut(sheet, 1, Row, 7, Row);
        //    int _row = Row;
        //    Row = Row + 1;
        //    sheet.Cells[Row, 1] = "合约";
        //    oper.SetRangeLayOut(sheet, 1, Row, 1, Row);
        //    sheet.Cells[Row, 2] = "买/卖";
        //    oper.SetRangeLayOut(sheet, 2, Row, 2, Row);
        //    sheet.Cells[Row, 3] = "成交价";
        //    oper.SetRangeLayOut(sheet, 3, Row, 3, Row);
        //    sheet.Cells[Row, 4] = "开仓价";
        //    oper.SetRangeLayOut(sheet, 4, Row, 4, Row);
        //    sheet.Cells[Row, 5] = "手数";
        //    oper.SetRangeLayOut(sheet, 5, Row, 5, Row);
        //    sheet.Cells[Row, 6] = "昨结算价";
        //    oper.UniteCells(sheet, 6, Row, 6, Row);
        //    sheet.Cells[Row, 7] = "平仓盈亏";
        //    oper.UniteCells(sheet, 7, Row, 7, Row);


        //    Row = Row + 1;

        //    int _volume = 0;
        //    decimal _benefit = 0;
        //    for (int i = 0; i < details.Count; i++)
        //    {
        //        if (details[i].Volume > 0)
        //        {
        //            sheet.Cells[Row, 1] = details[i].InstrumentId;
        //            oper.SetRangeLayOut(sheet, 1, Row, 1, Row);

        //            sheet.Cells[Row, 2] = details[i].Direction;
        //            oper.SetRangeLayOut(sheet, 2, Row, 2, Row);

        //            sheet.Cells[Row, 3] = details[i].TradePrice;
        //            oper.SetRangeLayOut(sheet, 3, Row, 3, Row);
        //            //     maichic += details[i].CloseAmount;
        //            sheet.Cells[Row, 4] = details[i].OpenPrice;
        //            oper.SetRangeLayOut(sheet, 4, Row, 4, Row);

        //            sheet.Cells[Row, 5] = details[i].Volume;
        //            oper.SetRangeLayOut(sheet, 5, Row, 5, Row);
        //            _volume += Convert.ToInt32(details[i].Volume);
        //            sheet.Cells[Row, 6] = details[i].YesterdaySettlement.ToString();
        //            oper.SetRangeLayOut(sheet, 6, Row, 6, Row);

        //            sheet.Cells[Row, 7] = details[i].CloseProfit.ToString();
        //            oper.SetRangeLayOut(sheet, 7, Row, 7, Row);
        //            _benefit += Convert.ToDecimal(details[i].CloseProfit);
        //            Row++;
        //        }
        //    }
        //    int cashiocount = details.Count + 2;
        //    excelRange = sheet.get_Range(sheet.Cells[_row, 1], sheet.Cells[_row + cashiocount, 7]);
        //    oper.SetBorder(excelRange);

        //    sheet.Cells[_row + cashiocount, 1] = "合计";
        //    // oper.UniteCells(sheet, 1, _row + cashiocount, 2, _row + cashiocount);
        //    oper.SetRangeLayOut(sheet, 1, _row + cashiocount, 2, _row + cashiocount);
        //    sheet.Cells[Row, 2] = "";
        //    oper.SetRangeLayOut(sheet, 2, Row, 2, Row);
        //    sheet.Cells[Row, 3] = "";
        //    oper.SetRangeLayOut(sheet, 3, Row, 3, Row);
        //    sheet.Cells[Row, 4] = "";
        //    oper.SetRangeLayOut(sheet, 4, Row, 4, Row);
        //    sheet.Cells[Row, 5] = _volume;
        //    oper.SetRangeLayOut(sheet, 5, Row, 5, Row);
        //    sheet.Cells[Row, 6] = "";
        //    oper.UniteCells(sheet, 6, Row, 6, Row);
        //    oper.SetRangeLayOut(sheet, 6, Row, 6, Row);

        //    sheet.Cells[Row, 7] = _benefit;
        //    oper.SetRangeLayOut(sheet, 7, Row, 7, Row);

        //    Row = _row + cashiocount + 1;
        //    return Row;
        //}




        /// <summary>
        /// 写入持仓明细
        /// </summary>
        /// <param name="accountid"></param>
        /// <param name="pdate"></param>
        /// <param name="sheet"></param>
        /// <param name="oper"></param>
        /// <param name="Row"></param>
        /// <returns></returns>
        private int DoCCMX(int accountid, int month, int year, Microsoft.Office.Interop.Excel.Worksheet sheet, Common.IO.ExcelOperator oper, int Row)
        {
            List<ServerHelper.Model.T_Position_Month> details = new ServerHelper.Model.T_Position_Month().GetInfo(accountid, year, month);
            sheet.Cells[Row, 1] = "持仓明细";
            oper.UniteCells(sheet, 1, Row, 10, Row);
            oper.SetRangeLayOut(sheet, 1, Row, 10, Row);
            int _row = Row;
            Row = Row + 1;
            sheet.Cells[Row, 1] = "合约";
            oper.SetRangeLayOut(sheet, 1, Row, 1, Row);
            sheet.Cells[Row, 2] = "成交序号";
            oper.SetRangeLayOut(sheet, 2, Row, 2, Row);
            sheet.Cells[Row, 3] = "买持仓";
            oper.SetRangeLayOut(sheet, 3, Row, 3, Row);
            sheet.Cells[Row, 4] = "买入价";
            oper.SetRangeLayOut(sheet, 4, Row, 4, Row);
            sheet.Cells[Row, 5] = "卖持仓";
            oper.SetRangeLayOut(sheet, 5, Row, 5, Row);
            sheet.Cells[Row, 6] = "卖出价";
            oper.UniteCells(sheet, 6, Row, 6, Row);
            sheet.Cells[Row, 7] = "昨结算价";
            oper.UniteCells(sheet, 7, Row, 7, Row);
            sheet.Cells[Row, 8] = "今结算价";
            oper.SetRangeLayOut(sheet, 8, Row, 8, Row);
            sheet.Cells[Row, 9] = "持仓盈亏";
            oper.SetRangeLayOut(sheet, 9, Row, 9, Row);
            sheet.Cells[Row, 10] = "投机/套保";
            oper.SetRangeLayOut(sheet, 10, Row, 10, Row);


            Row = Row + 1;

            decimal? maichic = 0;
            decimal? maichic2 = 0;
            decimal? benefit = 0;
            for (int i = 0; i < details.Count; i++)
            {
                if (details[i].Volume > 0)
                {
                    sheet.Cells[Row, 1] = details[i].InstrumentId;
                    oper.SetRangeLayOut(sheet, 1, Row, 1, Row);

                    sheet.Cells[Row, 2] = details[i].TradeId.ToString();
                    oper.SetRangeLayOut(sheet, 2, Row, 2, Row);
                    if (details[i].Direction.Equals("0"))
                    {
                        sheet.Cells[Row, 3] = details[i].CloseAmount.ToString();
                        oper.SetRangeLayOut(sheet, 3, Row, 3, Row);
                        maichic += details[i].CloseAmount;
                        sheet.Cells[Row, 4] = details[i].OpenPrice.ToString();
                        oper.SetRangeLayOut(sheet, 4, Row, 4, Row);

                        sheet.Cells[Row, 5] = "";
                        oper.SetRangeLayOut(sheet, 5, Row, 5, Row);
                        //     maichic2 += details[i].ShortPosition;
                        sheet.Cells[Row, 6] = "";
                        oper.SetRangeLayOut(sheet, 6, Row, 6, Row);
                    }
                    else
                    {
                        sheet.Cells[Row, 3] = "";
                        oper.SetRangeLayOut(sheet, 3, Row, 3, Row);
                        //     maichic += details[i].CloseAmount;
                        sheet.Cells[Row, 4] = "";
                        oper.SetRangeLayOut(sheet, 4, Row, 4, Row);

                        sheet.Cells[Row, 5] = details[i].CloseAmount.ToString();
                        oper.SetRangeLayOut(sheet, 5, Row, 5, Row);
                        maichic2 += details[i].CloseAmount;
                        sheet.Cells[Row, 6] = details[i].OpenPrice.ToString();
                        oper.SetRangeLayOut(sheet, 6, Row, 6, Row);
                    }


                    sheet.Cells[Row, 7] = details[i].YesterdaySettlement.ToString();
                    oper.SetRangeLayOut(sheet, 7, Row, 7, Row);
                    //    cje += details[i].TotalCost;

                    sheet.Cells[Row, 8] = details[i].SettlementPrice.ToString();
                    oper.SetRangeLayOut(sheet, 8, Row, 8, Row);

                    sheet.Cells[Row, 9] = details[i].PositionProfitByDate.ToString();
                    oper.SetRangeLayOut(sheet, 9, Row, 9, Row);
                    benefit += details[i].PositionProfitByDate;

                    sheet.Cells[Row, 10] = details[i].HedgeFlag.ToString();
                    oper.SetRangeLayOut(sheet, 10, Row, 10, Row);
                    Row++;
                }
            }
            int cashiocount = details.Count + 2;
            excelRange = sheet.get_Range(sheet.Cells[_row, 1], sheet.Cells[_row + cashiocount, 10]);
            oper.SetBorder(excelRange);

            sheet.Cells[_row + cashiocount, 1] = "合计";
            // oper.UniteCells(sheet, 1, _row + cashiocount, 2, _row + cashiocount);
            oper.SetRangeLayOut(sheet, 1, _row + cashiocount, 2, _row + cashiocount);
            sheet.Cells[Row, 2] = "";
            oper.SetRangeLayOut(sheet, 2, Row, 2, Row);
            sheet.Cells[Row, 3] = maichic.ToString();
            oper.SetRangeLayOut(sheet, 3, Row, 3, Row);
            sheet.Cells[Row, 4] = "";
            oper.SetRangeLayOut(sheet, 4, Row, 4, Row);
            sheet.Cells[Row, 5] = maichic2.ToString();
            oper.SetRangeLayOut(sheet, 5, Row, 5, Row);
            sheet.Cells[Row, 6] = "";
            oper.UniteCells(sheet, 6, Row, 6, Row);
            oper.SetRangeLayOut(sheet, 6, Row, 6, Row);

            sheet.Cells[Row, 7] = "";
            oper.SetRangeLayOut(sheet, 7, Row, 7, Row);
            sheet.Cells[Row, 8] = "";
            oper.SetRangeLayOut(sheet, 8, Row, 8, Row);
            sheet.Cells[Row, 9] = benefit.ToString();
            oper.SetRangeLayOut(sheet, 9, Row, 9, Row);
            sheet.Cells[Row, 10] = "";
            oper.SetRangeLayOut(sheet, 10, Row, 10, Row);
            Row = _row + cashiocount + 1;
            return Row;
        }

        /// <summary>
        /// 头信息
        /// </summary>
        /// <param name="sheet"></param>
        /// <param name="oper"></param>
        /// <param name="Row"></param>
        /// <returns></returns>
        private int WriteHead(int accountid, int month, int year, Microsoft.Office.Interop.Excel.Worksheet sheet, Common.IO.ExcelOperator oper, int Row)
        {
            Model.T_Account account = new ServerHelper.Model.T_Account();
            account.Id = accountid;
            object obj = Common.ADO.DBManager.GetModel(account);

            if (obj != null)
            {
                account = (Model.T_Account)obj;
                int customeId = Convert.ToInt32(account.CustomeId);
                Model.T_Custome custome = new DAL.T_Custome_DAL().GetModel(customeId.ToString());

                sheet.Cells[Row, 1] = "客户交易结算月报(逐日盯市)";
                oper.UniteCells(sheet, 1, Row, 10, Row);
                oper.SetRangeLayOut(sheet, 1, Row, 10, Row);
                Row = Row + 2;
                sheet.Cells[Row, 1] = "基本资料";
                oper.UniteCells(sheet, 1, Row, 10, Row);
                oper.SetRangeLayOut(sheet, 1, Row, 10, Row);
                excelRange = sheet.get_Range(sheet.Cells[4, 1], sheet.Cells[7, 10]);
                oper.SetBorder(excelRange);
                Row = Row + 1;
                sheet.Cells[Row, 1] = "客户内部资金账户";
                oper.UniteCells(sheet, 1, Row, 2, Row);
                sheet.Cells[Row, 3] = account.LoginAccount;
                oper.UniteCells(sheet, 3, Row, 5, Row);
                sheet.Cells[Row, 6] = "交易月份";
                oper.UniteCells(sheet, 6, Row, 7, Row);
                sheet.Cells[Row, 8] = year + "年" + month + "月";
                oper.UniteCells(sheet, 8, Row, 10, Row);
                Row = Row + 1;
                sheet.Cells[Row, 1] = "客户名称";
                oper.UniteCells(sheet, 1, Row, 2, Row);
                sheet.Cells[Row, 3] = custome.CustomeName;
                oper.UniteCells(sheet, 3, Row, 5, Row);
                sheet.Cells[Row, 6] = "查询时间";
                oper.UniteCells(sheet, 6, Row, 7, Row);
                sheet.Cells[Row, 8] = DateTime.Now.ToString();
                oper.UniteCells(sheet, 8, Row, 10, Row);
                Row = Row + 1;
                sheet.Cells[Row, 1] = "期货公司名称";
                oper.UniteCells(sheet, 1, Row, 2, Row);
                sheet.Cells[Row, 3] = "融航期货交易平台";//////////////////////////////////////////////
                oper.UniteCells(sheet, 3, Row, 5, Row);
                sheet.Cells[Row, 6] = "";
                oper.UniteCells(sheet, 6, Row, 7, Row);
                sheet.Cells[Row, 8] = "";
                oper.UniteCells(sheet, 8, Row, 10, Row);
                Row = Row + 2;
            }
            return Row;
        }
        /// <summary>
        /// 资金信息时间格式2010-02-02
        /// </summary>
        /// <param name="sheet"></param>
        /// <param name="oper"></param>
        /// <param name="Row"></param>
        /// <returns></returns>
        private int WriteZJZK(int accountid, string pdate, Microsoft.Office.Interop.Excel.Worksheet sheet, Common.IO.ExcelOperator oper, int Row)
        {
            ServerHelper.Model.T_SettlementMoney_Month smoney = new ServerHelper.Model.T_SettlementMoney_Month();

            ServerHelper.Model.T_SettlementMoney_Month s = new ServerHelper.Model.T_SettlementMoney_Month();

            smoney = s.GetInfo(accountid, Convert.ToDateTime(pdate));
            excelRange = sheet.get_Range(sheet.Cells[9, 1], sheet.Cells[15, 10]);
            oper.SetBorder(excelRange);
            sheet.Cells[Row, 1] = "资金状况";
            oper.UniteCells(sheet, 1, Row, 10, Row);
            oper.SetRangeLayOut(sheet, 1, Row, 10, Row);
            excelRange = sheet.get_Range(sheet.Cells[4, 1], sheet.Cells[7, 10]);
            oper.SetBorder(excelRange);
            Row = Row + 1;
            sheet.Cells[Row, 1] = "上日结存";
            oper.UniteCells(sheet, 1, Row, 2, Row);
            sheet.Cells[Row, 3] = smoney.PresettlePosit.ToString();
            oper.UniteCells(sheet, 3, Row, 5, Row);
            sheet.Cells[Row, 6] = "客户权益";
            oper.UniteCells(sheet, 6, Row, 7, Row);
            sheet.Cells[Row, 8] = smoney.ClientMoney;
            oper.UniteCells(sheet, 8, Row, 10, Row);
            Row = Row + 1;
            sheet.Cells[Row, 1] = "当月存取合计";
            oper.UniteCells(sheet, 1, Row, 2, Row);
            sheet.Cells[Row, 3] = smoney.DepositWithDraw;
            oper.UniteCells(sheet, 3, Row, 5, Row);
            sheet.Cells[Row, 6] = "质押金";
            oper.UniteCells(sheet, 6, Row, 7, Row);
            sheet.Cells[Row, 8] = smoney.Mortgage;
            oper.UniteCells(sheet, 8, Row, 10, Row);
            Row = Row + 1;
            sheet.Cells[Row, 1] = "当月盈亏";
            oper.UniteCells(sheet, 1, Row, 2, Row);
            sheet.Cells[Row, 3] = smoney.Profit;
            oper.UniteCells(sheet, 3, Row, 5, Row);
            sheet.Cells[Row, 6] = "保证金占用";
            oper.UniteCells(sheet, 6, Row, 7, Row);
            sheet.Cells[Row, 8] = smoney.Margin;
            oper.UniteCells(sheet, 8, Row, 10, Row);
            Row = Row + 1;
            sheet.Cells[Row, 1] = "当月手续费";
            oper.UniteCells(sheet, 1, Row, 2, Row);
            sheet.Cells[Row, 3] = smoney.Commission;
            oper.UniteCells(sheet, 3, Row, 5, Row);
            sheet.Cells[Row, 6] = "可用资金";
            oper.UniteCells(sheet, 6, Row, 7, Row);
            sheet.Cells[Row, 8] = smoney.AvailableMoney;
            oper.UniteCells(sheet, 8, Row, 10, Row);
            Row = Row + 1;
            sheet.Cells[Row, 1] = "当月结存";
            oper.UniteCells(sheet, 1, Row, 2, Row);
            sheet.Cells[Row, 3] = smoney.SettlePosit;
            oper.UniteCells(sheet, 3, Row, 5, Row);
            sheet.Cells[Row, 6] = "风险度";
            oper.UniteCells(sheet, 6, Row, 7, Row);
            sheet.Cells[Row, 8] = smoney.RiskRate;
            oper.UniteCells(sheet, 8, Row, 10, Row);
            Row = Row + 1;
            sheet.Cells[Row, 1] = "";
            oper.UniteCells(sheet, 1, Row, 2, Row);
            sheet.Cells[Row, 3] = "";//////////////////////////////////////////////
            oper.UniteCells(sheet, 3, Row, 5, Row);
            sheet.Cells[Row, 6] = "追加保证金";
            oper.UniteCells(sheet, 6, Row, 7, Row);
            sheet.Cells[Row, 8] = smoney.AppendMargin;
            oper.UniteCells(sheet, 8, Row, 10, Row);
            Row = Row + 2;
            return Row;
        }
        /// <summary>
        /// 传入的时间格式必须是2010-02-09
        /// </summary>
        /// <param name="accountid"></param>
        /// <param name="pdate"></param>
        /// <param name="sheet"></param>
        /// <param name="oper"></param>
        private void setZhuRiDingShiText(int accountid, int month, int year, Microsoft.Office.Interop.Excel.Worksheet sheet, Common.IO.ExcelOperator oper)
        {
            //    string date = pdate.Replace("-", "");
            int Row = 2;
            Row = WriteHead(accountid, month, year, sheet, oper, Row);//ok
            DateTime pdate = Convert.ToDateTime(year + "-" + month + "-01");
            Row = WriteZJZK(accountid, year + "-" + month + "-01", sheet, oper, Row);//ok
            Row = WriteCashIO(sheet, oper, Row, accountid, pdate.AddHours(2));//ok
            Row = WriteCJHZ(sheet, oper, Row, accountid, pdate);
            Row = WriteCCHZ(sheet, oper, Row, accountid, pdate);
            sheet.get_Range(sheet.Cells[1, 1], sheet.Cells[Row, 10]).RowHeight = 14;
        }
        /// <summary>
        /// 持仓汇总
        /// </summary>
        /// <param name="sheet"></param>
        /// <param name="oper"></param>
        /// <param name="Row"></param>
        /// <param name="AccountId"></param>
        /// <param name="Pdate"></param>
        /// <returns></returns>
        private int WriteCCHZ(Microsoft.Office.Interop.Excel.Worksheet sheet, Common.IO.ExcelOperator oper, int Row, int AccountId, DateTime Pdate)
        {
            List<ServerHelper.Model.T_Position_Summary_Month> details = new ServerHelper.Model.T_Position_Summary_Month().GetInfo(AccountId, Pdate.Year, Pdate.Month);
            sheet.Cells[Row, 1] = "持仓汇总";
            oper.UniteCells(sheet, 1, Row, 10, Row);
            oper.SetRangeLayOut(sheet, 1, Row, 10, Row);
            int _row = Row;
            Row = Row + 1;
            sheet.Cells[Row, 1] = "合约";
            oper.SetRangeLayOut(sheet, 1, Row, 1, Row);
            sheet.Cells[Row, 2] = "买持仓";
            oper.SetRangeLayOut(sheet, 2, Row, 2, Row);
            sheet.Cells[Row, 3] = "买均价";
            oper.SetRangeLayOut(sheet, 3, Row, 3, Row);
            sheet.Cells[Row, 4] = "卖持仓";
            oper.SetRangeLayOut(sheet, 4, Row, 4, Row);
            sheet.Cells[Row, 5] = "卖均价";
            oper.SetRangeLayOut(sheet, 5, Row, 5, Row);
            sheet.Cells[Row, 6] = "昨结算价";
            oper.UniteCells(sheet, 6, Row, 6, Row);
            sheet.Cells[Row, 7] = "今结算价";
            oper.UniteCells(sheet, 7, Row, 7, Row);
            sheet.Cells[Row, 8] = "持仓盈亏";
            oper.SetRangeLayOut(sheet, 8, Row, 8, Row);
            sheet.Cells[Row, 9] = "交易保证金";
            oper.SetRangeLayOut(sheet, 9, Row, 9, Row);
            sheet.Cells[Row, 10] = "投机/套保";
            oper.SetRangeLayOut(sheet, 10, Row, 10, Row);


            Row = Row + 1;

            int? maichic = 0;
            int? maichic2 = 0;
            decimal? benefit = 0;
            decimal? margin = 0;
            for (int i = 0; i < details.Count; i++)
            {
                sheet.Cells[Row, 1] = details[i].InstrumentId;
                oper.SetRangeLayOut(sheet, 1, Row, 1, Row);

                sheet.Cells[Row, 2] = details[i].LongPosition.ToString();
                oper.SetRangeLayOut(sheet, 2, Row, 2, Row);
                maichic += details[i].LongPosition;
                sheet.Cells[Row, 3] = details[i].LongAvgprice.ToString();
                oper.SetRangeLayOut(sheet, 3, Row, 3, Row);

                sheet.Cells[Row, 4] = details[i].ShortPosition.ToString();
                oper.SetRangeLayOut(sheet, 4, Row, 4, Row);
                maichic2 += details[i].ShortPosition;
                sheet.Cells[Row, 5] = details[i].ShortAvgprice.ToString();
                oper.SetRangeLayOut(sheet, 5, Row, 5, Row);
                //   totalbillcount += details[i].Volume;

                sheet.Cells[Row, 6] = details[i].YesterdaySettlement.ToString();
                oper.SetRangeLayOut(sheet, 6, Row, 6, Row);
                //    cje += details[i].TotalCost;

                sheet.Cells[Row, 7] = details[i].SettlementPrice.ToString();
                oper.SetRangeLayOut(sheet, 7, Row, 7, Row);

                sheet.Cells[Row, 8] = details[i].PositionProfit.ToString();
                oper.SetRangeLayOut(sheet, 8, Row, 8, Row);
                benefit += details[i].PositionProfit;

                sheet.Cells[Row, 9] = details[i].Margin.ToString();
                oper.SetRangeLayOut(sheet, 9, Row, 9, Row);
                margin += details[i].Margin;

                if (details[i].HedgeFlag.ToString().Equals("1"))
                {
                    sheet.Cells[Row, 10] = "投机";
                }
                else if (details[i].HedgeFlag.ToString().Equals("2"))
                {
                    sheet.Cells[Row, 10] = "套利";
                }
                else if (details[i].HedgeFlag.ToString().Equals("3"))
                {
                    sheet.Cells[Row, 10] = "套保";
                }
                oper.SetRangeLayOut(sheet, 10, Row, 10, Row);
                Row++;
            }
            int cashiocount = details.Count + 2;
            excelRange = sheet.get_Range(sheet.Cells[_row, 1], sheet.Cells[_row + cashiocount, 10]);
            oper.SetBorder(excelRange);

            sheet.Cells[_row + cashiocount, 1] = "合计";
            // oper.UniteCells(sheet, 1, _row + cashiocount, 2, _row + cashiocount);
            oper.SetRangeLayOut(sheet, 1, _row + cashiocount, 2, _row + cashiocount);
            sheet.Cells[Row, 2] = maichic.ToString();
            oper.SetRangeLayOut(sheet, 2, Row, 2, Row);
            sheet.Cells[Row, 3] = "";
            oper.SetRangeLayOut(sheet, 3, Row, 3, Row);
            sheet.Cells[Row, 4] = maichic2.ToString();
            oper.SetRangeLayOut(sheet, 4, Row, 4, Row);
            sheet.Cells[Row, 5] = "";
            oper.SetRangeLayOut(sheet, 5, Row, 5, Row);
            sheet.Cells[Row, 6] = "";
            oper.UniteCells(sheet, 6, Row, 6, Row);
            oper.SetRangeLayOut(sheet, 6, Row, 6, Row);

            sheet.Cells[Row, 7] = "";
            oper.SetRangeLayOut(sheet, 7, Row, 7, Row);
            sheet.Cells[Row, 8] = benefit.ToString();
            oper.SetRangeLayOut(sheet, 8, Row, 8, Row);
            sheet.Cells[Row, 9] = margin.ToString();
            oper.SetRangeLayOut(sheet, 9, Row, 9, Row);
            sheet.Cells[Row, 10] = "";
            oper.SetRangeLayOut(sheet, 10, Row, 10, Row);
            Row = _row + cashiocount + 1;
            return Row + 1;
        }


        /// <summary>
        /// 成交汇总
        /// </summary>
        /// <param name="sheet"></param>
        /// <param name="oper"></param>
        /// <param name="Row"></param>
        /// <param name="AccountId"></param>
        /// <param name="Pdate"></param>
        /// <returns></returns>
        private int WriteCJHZ(Microsoft.Office.Interop.Excel.Worksheet sheet, Common.IO.ExcelOperator oper, int Row, int AccountId, DateTime Pdate)
        {
            List<ServerHelper.Model.T_Trade_Summary_Month> details = new ServerHelper.Model.T_Trade_Summary_Month().GetInfo(AccountId, Pdate.Year, Pdate.Month);
            sheet.Cells[Row, 1] = "成交汇总";
            oper.UniteCells(sheet, 1, Row, 10, Row);
            oper.SetRangeLayOut(sheet, 1, Row, 10, Row);
            int _row = Row;
            Row = Row + 1;

            sheet.Cells[Row, 1] = "交易日期";
            oper.SetRangeLayOut(sheet, 1, Row, 1, Row);
            sheet.Cells[Row, 2] = "合约";
            oper.SetRangeLayOut(sheet, 2, Row, 2, Row);
            sheet.Cells[Row, 3] = "买/卖";
            oper.SetRangeLayOut(sheet, 3, Row, 3, Row);
            sheet.Cells[Row, 4] = "投机/套保";
            oper.SetRangeLayOut(sheet, 4, Row, 4, Row);
            sheet.Cells[Row, 5] = "成交价";
            oper.SetRangeLayOut(sheet, 5, Row, 5, Row);
            sheet.Cells[Row, 6] = "手数";
            oper.SetRangeLayOut(sheet, 6, Row, 6, Row);
            sheet.Cells[Row, 7] = "成交额";
            oper.SetRangeLayOut(sheet, 7, Row, 7, Row);
            sheet.Cells[Row, 8] = "开/平";
            oper.SetRangeLayOut(sheet, 8, Row, 8, Row);
            sheet.Cells[Row, 9] = "手续费";
            oper.SetRangeLayOut(sheet, 9, Row, 9, Row);
            sheet.Cells[Row, 10] = "平仓盈亏";
            oper.SetRangeLayOut(sheet, 10, Row, 10, Row);

            Row = Row + 1;

            decimal? totalbillcount = 0;
            decimal? cje = 0;
            decimal? fee = 0;
            decimal? benefit = 0;
            for (int i = 0; i < details.Count; i++)
            {
                sheet.Cells[Row, 1] = details[i].TradingDay;
                oper.SetRangeLayOut(sheet, 1, Row, 1, Row);

                sheet.Cells[Row, 2] = details[i].InstrumentId;
                oper.SetRangeLayOut(sheet, 2, Row, 2, Row);


                sheet.Cells[Row, 3] = details[i].Direction;
                oper.SetRangeLayOut(sheet, 3, Row, 3, Row);

                if (details[i].HedgeFlag.ToString().Equals("1"))
                {
                    sheet.Cells[Row, 4] = "投机";
                    oper.SetRangeLayOut(sheet, 4, Row, 4, Row);
                }
                else if (details[i].HedgeFlag.ToString().Equals("2"))
                {
                    sheet.Cells[Row, 4] = "套利";
                    oper.SetRangeLayOut(sheet, 4, Row, 4, Row);
                }
                else if (details[i].HedgeFlag.ToString().Equals("3"))
                {
                    sheet.Cells[Row, 4] = "套保";
                    oper.SetRangeLayOut(sheet, 4, Row, 4, Row);
                }

                sheet.Cells[Row, 5] = details[i].TradePrice.ToString();
                oper.SetRangeLayOut(sheet, 5, Row, 5, Row);

                sheet.Cells[Row, 6] = details[i].Volume.ToString();
                oper.SetRangeLayOut(sheet, 6, Row, 6, Row);
                totalbillcount += details[i].Volume;

                sheet.Cells[Row, 7] = details[i].TotalCost.ToString();
                oper.SetRangeLayOut(sheet, 7, Row, 7, Row);
                cje += details[i].TotalCost;

                if (details[i].OffsetFlag.ToString().Equals("0"))
                {
                    sheet.Cells[Row, 8] = "开仓";
                }
                else if (details[i].OffsetFlag.ToString().Equals("1"))
                {
                    sheet.Cells[Row, 8] = "平仓";
                }
                else if (details[i].OffsetFlag.ToString().Equals("2"))
                {
                    sheet.Cells[Row, 8] = "强平";
                }
                else if (details[i].OffsetFlag.ToString().Equals("3"))
                {
                    sheet.Cells[Row, 8] = "平今";
                }
                else if (details[i].OffsetFlag.ToString().Equals("4"))
                {
                    sheet.Cells[Row, 8] = "平昨";
                }
                else if (details[i].OffsetFlag.ToString().Equals("5"))
                {
                    sheet.Cells[Row, 8] = "强减";
                }
                else if (details[i].OffsetFlag.ToString().Equals("6"))
                {
                    sheet.Cells[Row, 8] = "本地强平";
                }
                oper.SetRangeLayOut(sheet, 8, Row, 8, Row);

                sheet.Cells[Row, 9] = details[i].Commission.ToString();
                oper.SetRangeLayOut(sheet, 9, Row, 9, Row);
                fee += details[i].Commission;

                sheet.Cells[Row, 10] = details[i].CloseProfit.ToString();
                oper.SetRangeLayOut(sheet, 10, Row, 10, Row);
                benefit += details[i].CloseProfit;

                Row++;
            }
            int cashiocount = details.Count + 2;
            excelRange = sheet.get_Range(sheet.Cells[_row, 1], sheet.Cells[_row + cashiocount, 10]);
            oper.SetBorder(excelRange);

            sheet.Cells[_row + cashiocount, 1] = "合计";
            oper.UniteCells(sheet, 1, _row + cashiocount, 2, _row + cashiocount);
            oper.SetRangeLayOut(sheet, 1, _row + cashiocount, 2, _row + cashiocount);
            sheet.Cells[Row, 2] = "";
            oper.SetRangeLayOut(sheet, 2, Row, 2, Row);
            sheet.Cells[Row, 3] = "";
            oper.SetRangeLayOut(sheet, 3, Row, 3, Row);
            sheet.Cells[Row, 4] = "";
            oper.SetRangeLayOut(sheet, 4, Row, 4, Row);
            sheet.Cells[Row, 5] = "";
            oper.SetRangeLayOut(sheet, 4, Row, 5, Row);
            sheet.Cells[Row, 6] = totalbillcount.ToString();
            oper.SetRangeLayOut(sheet, 6, Row, 6, Row);
            sheet.Cells[Row, 7] = cje.ToString();
            oper.SetRangeLayOut(sheet, 7, Row, 7, Row);
            sheet.Cells[Row, 8] = "";
            oper.SetRangeLayOut(sheet, 8, Row, 8, Row);
            sheet.Cells[Row, 9] = fee.ToString();
            oper.SetRangeLayOut(sheet, 9, Row, 9, Row);
            sheet.Cells[Row, 10] = benefit.ToString();
            oper.SetRangeLayOut(sheet, 10, Row, 10, Row);
            Row = _row + cashiocount + 1;
            return Row + 1;

        }


        /// <summary>
        /// 出入金明细
        /// </summary>
        /// <param name="sheet"></param>
        /// <param name="oper"></param>
        /// <param name="Row"></param>
        /// <param name="AccountId"></param>
        /// <param name="Pdate"></param>
        /// <returns></returns>
        private int WriteCashIO(Microsoft.Office.Interop.Excel.Worksheet sheet, Common.IO.ExcelOperator oper, int Row, int AccountId, DateTime Pdate)
        {
            sheet.Cells[Row, 1] = "出入金明细";
            oper.UniteCells(sheet, 1, Row, 10, Row);
            oper.SetRangeLayOut(sheet, 1, Row, 10, Row);
            int _row = Row;
            Row = Row + 1;
            sheet.Cells[Row, 1] = "发生日期";
            oper.UniteCells(sheet, 1, Row, 2, Row);
            oper.SetRangeLayOut(sheet, 1, Row, 2, Row);
            sheet.Cells[Row, 3] = "入金";
            oper.UniteCells(sheet, 3, Row, 4, Row);
            oper.SetRangeLayOut(sheet, 3, Row, 4, Row);
            sheet.Cells[Row, 5] = "出金";
            oper.UniteCells(sheet, 5, Row, 6, Row);
            oper.SetRangeLayOut(sheet, 5, Row, 6, Row);
            sheet.Cells[Row, 7] = "方式";
            oper.UniteCells(sheet, 7, Row, 8, Row);
            oper.SetRangeLayOut(sheet, 7, Row, 8, Row);
            sheet.Cells[Row, 9] = "摘要";
            oper.UniteCells(sheet, 9, Row, 10, Row);
            oper.SetRangeLayOut(sheet, 9, Row, 10, Row);
            Row = Row + 1;
            Model.T_CashIO io = new ServerHelper.Model.T_CashIO();
            io.AccountId = AccountId;
            io.Pdate = Pdate;
            //取得前一天的出入金明细
            List<ServerHelper.Model.T_CashIO_History> cash = new Model.T_CashIO_History().GetMonthInfo(AccountId, Pdate.Month, Pdate.Year);
            decimal? totalmoney = 0;
            decimal? cashin = 0;
            decimal? cashout = 0;
            for (int i = 0; i < cash.Count; i++)
            {
                if (cash[i].Force.ToString().Equals("转入"))
                {
                    totalmoney += cash[i].Amount;
                    cashin += cash[i].Amount;
                }
                else
                {
                    totalmoney -= cash[i].Amount;
                    cashout += cash[i].Amount;
                }
            }

            for (int i = 0; i < cash.Count; i++)
            {
                sheet.Cells[Row, 1] = cash[i].Pdate.ToString();
                oper.UniteCells(sheet, 1, Row, 2, Row);
                oper.SetRangeLayOut(sheet, 1, Row, 2, Row);
                if (cash[i].Force.Trim().ToString().Equals("转入"))
                {
                    sheet.Cells[Row, 3] = cash[i].Amount.ToString();
                    oper.UniteCells(sheet, 3, Row, 4, Row);
                }
                else
                {
                    sheet.Cells[Row, 3] = "";
                    oper.UniteCells(sheet, 3, Row, 4, Row);
                    oper.SetRangeLayOut(sheet, 3, Row, 4, Row);
                }
                if (cash[i].Force.Trim().ToString().Equals("转出"))
                {
                    sheet.Cells[Row, 5] = cash[i].Amount.ToString();
                    oper.UniteCells(sheet, 5, Row, 6, Row);
                    oper.SetRangeLayOut(sheet, 5, Row, 6, Row);
                }
                else
                {
                    sheet.Cells[Row, 5] = "";
                    oper.UniteCells(sheet, 5, Row, 6, Row);
                    oper.SetRangeLayOut(sheet, 5, Row, 6, Row);
                }

                sheet.Cells[Row, 7] = "";
                oper.UniteCells(sheet, 7, Row, 8, Row);
                oper.SetRangeLayOut(sheet, 7, Row, 8, Row);
                sheet.Cells[Row, 9] = "";
                oper.UniteCells(sheet, 9, Row, 10, Row);
                oper.SetRangeLayOut(sheet, 9, Row, 10, Row);
                Row++;
            }
            int cashiocount = cash.Count + 2;
            excelRange = sheet.get_Range(sheet.Cells[_row, 1], sheet.Cells[_row + cashiocount, 10]);
            oper.SetBorder(excelRange);

            sheet.Cells[_row + cashiocount, 1] = "合计";
            oper.UniteCells(sheet, 1, _row + cashiocount, 2, _row + cashiocount);
            oper.SetRangeLayOut(sheet, 1, _row + cashiocount, 2, _row + cashiocount);
            sheet.Cells[_row + cashiocount, 3] = cashin.ToString();
            oper.UniteCells(sheet, 3, _row + cashiocount, 4, _row + cashiocount);
            oper.SetRangeLayOut(sheet, 3, _row + cashiocount, 4, _row + cashiocount);
            sheet.Cells[_row + cashiocount, 5] = cashout.ToString();
            oper.UniteCells(sheet, 5, _row + cashiocount, 6, _row + cashiocount);
            oper.SetRangeLayOut(sheet, 5, _row + cashiocount, 6, _row + cashiocount);
            sheet.Cells[_row + cashiocount, 7] = "";
            oper.UniteCells(sheet, 7, _row + cashiocount, 8, _row + cashiocount);
            oper.SetRangeLayOut(sheet, 7, _row + cashiocount, 8, _row + cashiocount);
            sheet.Cells[_row + cashiocount, 9] = "";
            oper.UniteCells(sheet, 9, _row + cashiocount, 10, _row + cashiocount);
            oper.SetRangeLayOut(sheet, 9, _row + cashiocount, 10, _row + cashiocount);
            Row = _row + cashiocount + 1;
            return Row + 1;
        }
    }
}

         * */
    }
}
